package com.liwen.kefu.service.impl;

import com.liwen.kefu.service.InitDataService;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;

import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.Map;

/**
 * @author: Liwen
 * @createTime: 2024/11/1
 * @description:
 */
@Service
public class InitDataServiceImpl implements InitDataService {


    /**
     * 获取某张表删除的key
     *
     * @param dbName
     * @param tableName
     * @return
     */
    @Override
    public String initKeys(String dbName, String tableName) {
        HashMap<String, String> idsMap = new HashMap<>();
        // 借款订单
        idsMap.put("ccsdb.ccs_customer", "cust_id");
        idsMap.put("ccsdb.ccs_acct", "acct_nbr");
        idsMap.put("ccsdb.ccs_loan", "acct_nbr");
        idsMap.put("ccsdb.ccs_plan", "acct_nbr");
        idsMap.put("ccsdb.ccs_order", "acct_nbr");
        idsMap.put("ccsdb.ccs_order", "acct_nbr");
        idsMap.put("ccsdb.ccs_order", "acct_nbr");
        idsMap.put("ccsdb.ccs_asset_sell_info", "f_contr_nbr");
        // 新核心
        idsMap.put("db_gmcf_ofc.t_ofc_loan_order", "f_user_id");
        idsMap.put("db_gmcf_bec.t_bec_bidding", "f_bidding_no");
        idsMap.put("db_gmcf_dec.t_dec_debit", "f_bidding_no");
        idsMap.put("db_gmcf_lsc.t_lsc_repayment", "f_bidding_no");
        idsMap.put("db_gmcf_lsc.t_lsc_repayment", "f_bidding_no");
        idsMap.put("db_gmcf_rec.t_rec_right", "f_bidding_no");
        // 资方
        idsMap.put("db_gmcf_bec.t_bec_investor", "f_bidding_no");
        // 订单级别归属状态
        idsMap.put("ermasdb.ermas_case_main", "acct_nbr");

        // 会员
        idsMap.put("db_gmcf_emc.t_emc_service_order", "f_user_tel");
        idsMap.put("db_gmcf_emc.t_emc_order", "f_order_no");
        idsMap.put("db_gmcf_emc.t_emc_order_item", "f_order_no");
        idsMap.put("db_gmcf_emc.t_emc_order_refund", "f_user_tel");
        idsMap.put("db_gmcf_emc.t_emc_bill_info", "f_mall_order_no");
        idsMap.put("db_gmcf_emc.t_emc_bill_detail", "f_bill_no");
        // 查询订单/ 订单状态和时间
        idsMap.put("db_gmcf_emc.t_emc_order_record", "f_order_no");
        // 权益明细/权益名称
        idsMap.put("db_gmcf_emc.t_emc_privilege", "f_id");
        // 券id
        idsMap.put("db_gmcf_emc.t_emc_privilege_record", "f_user_id");
        // 券记录， 进件号LOC
        idsMap.put("united_coupon.t_sale_coupon_user_cell_state", "f_user_id");
        // 加速卡
        idsMap.put("db_gmcf_emc.t_emc_service_loan_relation", "f_service_no");




        String deleteKey = idsMap.get(dbName + "." + tableName);

        return deleteKey;
    }

    /**
     * 根据表名，获取hive的查询sql
     *
     * @param userId
     * @param mobileNo
     * @param tableName
     * @return
     */
    @Override
    public String initQuerySql(String userId, String mobileNo, String tableName) {
        HashMap<String, String> sqlMap = new HashMap<>();
        //============================《借款订单》============================================
        // ===========================《新核心》==============================================
        String ofcLoanOrderSql = "select *  from odsdb_gmcf_ofc.t_ofc_loan_order where f_user_id in ('${user_id}') and f_bidding_no <> 'NULL' order by f_update_time desc";
        sqlMap.put("t_ofc_loan_order", ofcLoanOrderSql);
        String becBiddingSql = "select * from odsdb_gmcf_bec.t_bec_bidding where f_bidding_no in(\n" +
                "    select f_bidding_no  from odsdb_gmcf_ofc.t_ofc_loan_order \n" +
                "    where f_user_id in ('${user_id}')\n" +
                "    and f_bidding_no <> 'NULL'\n" +
                ") order by f_created_time DESC";
        sqlMap.put("t_bec_bidding", becBiddingSql);
        String decDebitSql = "select * from odsdb_gmcf_dec.t_dec_debit where f_bidding_no in (\n" +
                "    select f_bidding_no  from odsdb_gmcf_ofc.t_ofc_loan_order \n" +
                "    where f_user_id in ('${user_id}')\n" +
                "    and f_bidding_no <> 'NULL'\n" +
                ") order by f_created_time DESC";
        sqlMap.put("t_dec_debit", decDebitSql);
        String lscRepaymentSql = "select * from odsdb_gmcf_lsc.t_lsc_repayment where f_bidding_no in(\n" +
                "    select f_bidding_no  from odsdb_gmcf_ofc.t_ofc_loan_order \n" +
                "    where f_user_id in ('${user_id}')\n" +
                "    and f_bidding_no <> 'NULL'\n" +
                ") order by f_created_time DESC";
        sqlMap.put("t_lsc_repayment", lscRepaymentSql);
        String recRightSql = "select * from odsdb_gmcf_rec.t_rec_right where f_bidding_no in(\n" +
                "    select f_bidding_no  from odsdb_gmcf_ofc.t_ofc_loan_order \n" +
                "    where f_user_id in ('${user_id}')\n" +
                "    and f_bidding_no <> 'NULL'\n" +
                ") order by f_created_time DESC";
        sqlMap.put("t_rec_right", recRightSql);
        String becInvestorSql = "select * from odsdb_gmcf_bec.t_bec_investor where f_bidding_no in(\n" +
                "    select f_bidding_no  from odsdb_gmcf_ofc.t_ofc_loan_order \n" +
                "    where f_user_id in ('${user_id}')\n" +
                "    and f_bidding_no <> 'NULL'\n" +
                ") order by f_created_time DESC";
        sqlMap.put("t_bec_investor", becInvestorSql);
        String ermasCaseMainSql = "select * from ermasdb.ermas_case_main ecm where CONTR_NBR in(\n" +
                "    select f_bidding_no  from odsdb_gmcf_ofc.t_ofc_loan_order \n" +
                "    where f_user_id in ('${user_id}')\n" +
                "    and f_bidding_no <> 'NULL'\n" +
                ") order by create_time DESC";
        sqlMap.put("ermas_case_main", ermasCaseMainSql);
        String becExpandRecordSq = "SELECT * FROM odsdb_gmcf_bec.t_bec_expand_record where f_bidding_no in (\n" +
                "    select f_bidding_no  from odsdb_gmcf_ofc.t_ofc_loan_order \n" +
                "    where f_user_id in ('${user_id}')\n" +
                "    and f_bidding_no <> 'NULL'\n" +
                ") order by f_created_time DESC";
        sqlMap.put("t_bec_expand_record", becExpandRecordSq);
        // ==============================《老核心》============================================
        String ccsCustomerSql = "select * from ccsdb.ccs_customer where mobile_no=\"${mobile_no}\"";
        sqlMap.put("ccs_customer", ccsCustomerSql);
        String ccsAcctSql = "select * from  ccsdb.ccs_acct where cust_id in(\n" +
                "    select cust_id from ccsdb.ccs_customer where mobile_no=\"${mobile_no}\"\n" +
                ");";
        sqlMap.put("ccs_acct", ccsAcctSql);
        String ccsLoanSql = "select * from ccsdb.ccs_loan where acct_nbr in (\n" +
                "    select acct_nbr from  ccsdb.ccs_acct where cust_id in(\n" +
                "        select cust_id from ccsdb.ccs_customer where mobile_no=\"${mobile_no}\"\n" +
                "    )\n" +
                ");";
        sqlMap.put("ccs_loan", ccsLoanSql);
        String ccsPlanSql = "select * from ccsdb.ccs_plan where acct_nbr in(\n" +
                "    select acct_nbr from  ccsdb.ccs_acct where cust_id in(\n" +
                "        select cust_id from ccsdb.ccs_customer where mobile_no=\"${mobile_no}\"\n" +
                "    )\n" +
                ");";
        sqlMap.put("ccs_plan", ccsPlanSql);
        String ccsOrder = "select * from ccsdb.ccs_order where acct_nbr in(\n" +
                "    select acct_nbr from  ccsdb.ccs_acct where cust_id in(\n" +
                "        select cust_id from ccsdb.ccs_customer where mobile_no=\"${mobile_no}\"\n" +
                "    )\n" +
                ");";
        sqlMap.put("ccs_order", ccsOrder);

        //============================《会员》============================================
        String emcServiceOrder = "select * from odsdb_gmcf_emc.t_emc_service_order where f_user_tel=\"${mobile_no}\";";
        sqlMap.put("t_emc_service_order", emcServiceOrder);
        String emcOrder = "select * from odsdb_gmcf_emc.t_emc_order \n" +
                "where f_order_no in(\n" +
                "    select f_mall_order_no from odsdb_gmcf_emc.t_emc_service_order where f_user_tel=\"${mobile_no}\"\n" +
                ")";
        sqlMap.put("t_emc_order", emcOrder);
        String emcOrderItem = "SELECT oi.* FROM odsdb_gmcf_emc.t_emc_order_item as oi \n" +
                "left join odsdb_gmcf_emc.t_emc_service_order as so \n" +
                "on (so.f_order_no = oi.f_service_order_no and oi.f_service_order_no is  not null ) or (so.f_mall_order_no = oi.f_order_no and oi.f_service_order_no is  null ) \n" +
                "where so.f_user_tel='${mobile_no}'";
        sqlMap.put("t_emc_order_item", emcOrderItem);
        String emcBillInfo = "select * from odsdb_gmcf_emc.t_emc_bill_info where f_mall_order_no in (\n" +
                "    select f_mall_order_no from odsdb_gmcf_emc.t_emc_service_order where f_user_tel=\"${mobile_no}\"\n" +
                ")";
        sqlMap.put("t_emc_bill_info", emcBillInfo);

        String emcBillDetail = "select * from odsdb_gmcf_emc.t_emc_bill_detail\n" +
                "where f_bill_no in (\n" +
                "    select f_bill_no from odsdb_gmcf_emc.t_emc_bill_info\n" +
                "    where f_mall_order_no in (\n" +
                "        select f_mall_order_no from odsdb_gmcf_emc.t_emc_service_order \n" +
                "        where f_user_tel=\"${mobile_no}\"\n" +
                "    ) \n" +
                ")";
        sqlMap.put("t_emc_bill_detail", emcBillDetail);
        // 查询订单/订单状态和时间
        String emcOrderRecord = "select* from odsdb_gmcf_emc.t_emc_order_record where f_order_no in(\n" +
                "    select f_mall_order_no from odsdb_gmcf_emc.t_emc_service_order where f_user_tel=\"${mobile_no}\"\n" +
                ");";
        sqlMap.put("t_emc_order_record", emcOrderRecord);
        // 权益明细/权益名称
        String emcPrivilege = "select * from odsdb_gmcf_emc.t_emc_privilege where f_id in (\n" +
                "    select f_privilege_id from odsdb_gmcf_emc.t_emc_privilege_record where f_order_no in (\n" +
                "        select so.f_order_no from odsdb_gmcf_emc.t_emc_order as  o\n" +
                "        left join odsdb_gmcf_emc.t_emc_service_order so  on so.f_mall_order_no = o.f_order_no\n" +
                "        where so.f_mall_order_no is not null\n" +
                "        and so.f_user_tel =\"${mobile_no}\"\n" +
                "    )\n" +
                ")";
        sqlMap.put("t_emc_privilege", emcPrivilege);

        // 券id
        String emcPrivilegeRecord = "select * from odsdb_gmcf_emc.t_emc_privilege_record where f_order_no in (\n" +
                "        select so.f_order_no from odsdb_gmcf_emc.t_emc_order as  o\n" +
                "        left join odsdb_gmcf_emc.t_emc_service_order so  on so.f_mall_order_no = o.f_order_no\n" +
                "        where so.f_mall_order_no is not null\n" +
                "        and so.f_user_tel =\"${mobile_no}\"\n" +
                " ) ";
        sqlMap.put("t_emc_privilege_record", emcPrivilegeRecord);
        // 券记录
        String saleCouponUserCellState = "select * from odsdb_united_coupon.t_sale_coupon_user_cell_state where f_user_id= \"${user_id}\" and\n" +
                " f_coupon_user_cell_id in (\n" +
                "  \n" +
                "    select cast(f_coupon_id as bigint) as f_coupon_id from odsdb_gmcf_emc.t_emc_privilege_record where f_order_no in (\n" +
                "        select so.f_order_no from odsdb_gmcf_emc.t_emc_order as  o\n" +
                "        left join odsdb_gmcf_emc.t_emc_service_order so  on so.f_mall_order_no = o.f_order_no\n" +
                "        where so.f_mall_order_no is not null\n" +
                "        and so.f_user_tel =\"${mobile_no}\"\n" +
                "    )\n" +
                " \n" +
                " )";
        sqlMap.put("t_sale_coupon_user_cell_state", saleCouponUserCellState);
        // 加速卡
        String emcServiceLoanRelation = "select * from odsdb_gmcf_emc.t_emc_service_loan_relation where f_service_no in (\n" +
                "    select f_order_no from odsdb_gmcf_emc.t_emc_service_order where f_user_tel=\"${mobile_no}\"\n" +
                ")";
        sqlMap.put("t_emc_service_loan_relation", emcServiceLoanRelation);
        String emcOrderRefund="select * from odsdb_gmcf_emc.t_emc_order_refund where f_user_tel=\"${mobile_no}\";";
        sqlMap.put("t_emc_order_refund",emcOrderRefund);

        String sql = sqlMap.get(tableName);
        if (StringUtils.isEmpty(sql)) {
            throw new NullPointerException("map中未找到:" + tableName + "的查询sql");
        }


        String sqlFormat = sql.replaceAll("\\$\\{user_id\\}", userId).replaceAll("\\$\\{mobile_no\\}", mobileNo);
        return sqlFormat;


    }

    /**
     * 初始化mysql 的库、表
     *
     * @param type
     * @return
     */
    @Override
    public Map<String, String> initMysqlTableName(String type) {
        Map<String, String> map = new LinkedHashMap<>();
        if (StringUtils.isNotEmpty(type) && "new".equals(type)) {
            map.put("t_ofc_loan_order", "db_gmcf_ofc");
            map.put("t_bec_bidding", "db_gmcf_bec");
            map.put("t_dec_debit", "db_gmcf_dec");
            map.put("t_lsc_repayment", "db_gmcf_lsc");
            map.put("t_rec_right", "db_gmcf_rec");
            map.put("t_bec_investor", "db_gmcf_bec");
        }
        if (StringUtils.isNotEmpty(type) && "old".equals(type)) {
            map.put("ccs_customer", "ccsdb");
            map.put("ccs_acct", "ccsdb");
            map.put("ccs_loan", "ccsdb");
            map.put("ccs_plan", "ccsdb");
            map.put("ccs_order", "ccsdb");
        }
        if (StringUtils.isNotEmpty(type) && "emc".equals(type)) {
            map.put("t_emc_service_order", "db_gmcf_emc");
            map.put("t_emc_order", "db_gmcf_emc");
            map.put("t_emc_order_item", "db_gmcf_emc");
            map.put("t_emc_bill_info", "db_gmcf_emc");
            map.put("t_emc_bill_detail", "db_gmcf_emc");
            // 退款时间
            map.put("t_emc_order_refund", "db_gmcf_emc");
            map.put("t_emc_order_record", "db_gmcf_emc"); // 查询订单/状态和时间
            map.put("t_emc_privilege", "db_gmcf_emc"); // 权益明细/权益名称
            map.put("t_emc_privilege_record", "db_gmcf_emc");// 券id
            map.put("t_sale_coupon_user_cell_state", "united_coupon");// 券记录，展示进件号
            // 加速卡
            map.put("t_emc_service_loan_relation", "db_gmcf_emc");



        }
        return map;

    }


}
